// Created by Windward Studios - no copyright is claimed. This code can be used in
// any manner by anyone for any reason. There is no copyright of any kind on it. You may
// use it in commercial products. You may change it without sharing those changes.
// We ask that you keep the "created by Windward Studios" in a comment at the top.

using System.Collections;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.IO;
using Kailua.net.windward.utils;
using Microsoft.Win32;

// bugbug
// 1: implement get stored procedures
// 3: get schema owner for tables, views, stored procedures
// 3: unit test both MS and Oracle connectors
// 4: get table/view/stored procedure/column descriptions.
// 4: get stored procedures variables (type & description)
// this may help - http://msdn.microsoft.com/en-us/library/ms254969(VS.80).aspx

namespace net.windward.utils.ado.Oracle
{
	/// <summary>
	/// This is similiar to DbProviderFactory and provides additional functionality for each provider. All properties to list
	/// if a provider can perform any task in it's child classes is provider here, not in the child classes. This
	/// class can return IWrServer objects for a given server/instance that exists on a computer. The ODBC/OleDb
	/// concept of a Provider (which is different from a DbProviderFactory) is handled as a property set in the
	/// WrProviderFactory class so all other cases do not need to implement a class for that layer.
	/// This is called WrVendor instead of WrProvider because it is different from DbProviderFactory and because
	/// we use the term Provider for the ODBC/OleDb providers.
	/// This is the implementation for the Oracle databases.
	/// </summary>
	public class WrOracleVendor : WrVendor
	{
		/// <summary>
		/// The DbProviderFactory class for this vendor
		/// </summary>
		public const string FactoryClass = "Oracle.DataAccess.Client";

		/// <summary>
		/// The .NET Oracle class. Use is not recomended.
		/// </summary>
		public const string FactoryClassFramework = "System.Data.OracleClient";

		/// <summary>
		/// The suggested name for this vendor.
		/// </summary>
		public const string FactoryName = "Oracle";

		/// <summary>
		/// Creates an Oracle vendor object.
		/// </summary>
		public WrOracleVendor()
			: base(FactoryName, "The standard MS .NET Oracle connector", FactoryClass)
		{
			syntax = new WrOracleSyntax(providerFactory);
		}

		/// <summary>
		/// Creates an ODBC vendor object.
		/// </summary>
		/// <param name="name">The friendly name of the provider.</param>
		/// <param name="description">The description of this provider (optional).</param>
		/// <param name="providerClass">The class of the provider.</param>
		public WrOracleVendor(string name, string description, string providerClass)
			: base(name, description, providerClass)
		{
			syntax = new WrOracleSyntax(providerFactory);
		}

		#region WrVendor properties

		/// <summary>
		/// True if can enumerate running servers from this vendor. This is implemented seperately from
		/// CanCreateDataSourceEnumerator because DB2 and SqlServer implement CreateDataSourceEnumerator differently.
		/// </summary>
		public override bool CanEnumerateServers
		{
			get
			{
				// first we try the provider
				if ((providerFactory != null) && providerFactory.CanCreateDataSourceEnumerator)
				{
					DbDataSourceEnumerator dsEnum = providerFactory.CreateDataSourceEnumerator();
					if (dsEnum != null)
					{
						DataTable servers = dsEnum.GetDataSources();

						if (servers.Rows.Count > 0)
						{
							Trap.trap();
							return true;
						}
					}
				}

				// now we do it the hard way
				TNSParser.TNSEntry[] entries = TNSParser.GetAllEntries();
				return entries.Length > 0;
			}
		}
		
		/// <summary>
		/// True if can enumerate databases on a server. This is implemented seperately from
		/// CanCreateDataSourceEnumerator because DB2 and SqlServer implement CreateDataSourceEnumerator differently.
		/// </summary>
		public override bool CanEnumerateDatabases
		{
			get { return false; }
		}

        /// <summary>
        /// True if can query metadata from the database.
        /// </summary>
        public override bool CanQueryMetadata
        {
            get { return true; }
        }

		/// <summary>
		/// True if can launch the administrator for this vendor. This will return true if it can find the admin
		/// program file. In that case the launch could still fail.
		/// </summary>
		public override bool CanLaunchAdministrator
		{
			get
			{
				return GetAdminFilename() != null;
			}
		}

		/// <summary>
		/// True if can run DDL scripts.
		/// </summary>
		public override bool CanRunScripts
		{
			get { return false; }
		}

		/// <summary>
		/// True if can use the WindowsIdentity of the caller eliminating the need to pass a username and password.
		/// </summary>
		public override bool CanUseTrustedConnection
		{
			get { return false; }
		}

		/// <summary>
		/// All servers from this vendor on the network. This may not get all due to routers not passing
		/// on a broadcast or delays in some servers responding.
		/// </summary>
		/// <exception cref="WrDbException">Thrown if this provider does not support enumerate servers.</exception>
		/// <returns>All servers found on the sub-net.</returns>
		public override IWrServer[] GetServers()
		{
			ArrayList rtn = new ArrayList();
			DbDataSourceEnumerator dsEnum = providerFactory.CreateDataSourceEnumerator();
			if (dsEnum != null)
			{
				DataTable servers = dsEnum.GetDataSources();
				int ordServer = servers.Columns.IndexOf("ServerName");
				int ordInstance = servers.Columns.IndexOf("InstanceName");
				Trap.trap(servers.Rows.Count > 0);
				foreach (DataRow row in servers.Rows)
				{
					string server = (string) row[ordServer];
					string instance = (ordInstance == -1) || row.IsNull(ordInstance) ? null : (string) row[ordInstance];
					if (!string.IsNullOrEmpty(instance))
						server += "\\" + instance;
					rtn.Add(new WrOracleServer(providerFactory, server));
				}

				rtn.Sort();
				return (WrOracleServer[]) rtn.ToArray(typeof (WrOracleServer));
			}

			TNSParser.TNSEntry[] entries = TNSParser.GetAllEntries();
			foreach (TNSParser.TNSEntry entry in entries)
				rtn.Add(new WrOracleServer(providerFactory, entry.Name));
			rtn.Sort();
			return (WrOracleServer[])rtn.ToArray(typeof(WrOracleServer));
		}

		/// <summary>
		/// True if this vendor has the conecpt of multiple databases in an installed copy of their product. The only
		/// known case where this is false is Oracle.
		/// </summary>
		public override DATABASE_MODE DatabaseMode
		{
			get { return DATABASE_MODE.NEVER; }
		}

		/// <summary>
		/// Returns true if the Oracle client is installed on this system (required for OracleConnection to work).
		/// </summary>
		public override bool IsInstalled
		{
			get
			{
				return providerFactory != null;
			}
		}

		#endregion

		#region WrVendor methods
		/// <summary>
		/// A connection string for a database on a server.
		/// </summary>
		/// <param name="server">The server to access (ex: localhost).</param>
		/// <param name="database">The database to connect to.</param>
		/// <param name="credentials">The user credentials to access the database.</param>
		/// <param name="showPassword">True to include the password, false to add the password as *****.</param>
		/// <returns>The connection string for this database on this server.</returns>
		public override string ConnectionString(string server, string database, WrCredentials credentials, bool showPassword)
		{
			return ConnectionString(providerFactory, server, database, credentials, showPassword);
		}

		internal static string ConnectionString(DbProviderFactory provider, string server, string database, WrCredentials credentials, bool showPassword)
		{

			DbConnectionStringBuilder connStrBuilder = provider.CreateConnectionStringBuilder();
			if (!string.IsNullOrEmpty(server))
				connStrBuilder.Add("Data Source", server);
			connStrBuilder.Add("Persist Security Info", "True");
			if (!credentials.UseWindowsIdentity)
			{
				if (!string.IsNullOrEmpty(credentials.Username))
					connStrBuilder.Add("User ID", credentials.Username);
				if (!string.IsNullOrEmpty(credentials.Password))
					connStrBuilder.Add("Password", showPassword ? credentials.Password : "*****");
			}
			return connStrBuilder.ConnectionString;
		}

		/// <summary>
		/// Returns the parameters from a connection string making the best guess. Will return null for items it could not determine.
		/// </summary>
		/// <param name="connectionString">The connection string to parse.</param>
		/// <returns>The parameters.</returns>
		public override WrConnectionParams ConnectionParams(string connectionString)
		{

			if (string.IsNullOrEmpty(connectionString))
				return new WrConnectionParams();

			Trap.trap();
			System.Data.Common.DbConnectionStringBuilder builder = providerFactory.CreateConnectionStringBuilder();
			Trap.trap(builder.GetType().FullName.StartsWith("System.Data.OracleClient."));
			Trap.trap(! builder.GetType().FullName.StartsWith("System.Data.OracleClient."));
			builder.ConnectionString = connectionString;
			return new WrConnectionParams(builder["server"] as string, null, builder["user id"] as string, builder["password"] as string);
		}

		/// <summary>
		/// Returns a new instance of the provider's class that implements the WrCommand class.
		/// </summary>
		/// <returns>A new instance of WrCommand.</returns>
		public override WrCommand CreateCommand()
		{
			Trap.trap();
			return new WrOracleCommand(providerFactory);
		}

		/// <summary>
		/// Create a server object.
		/// </summary>
		/// <param name="server">The name of the server the database is on.</param>
		public override IWrServer CreateServer(string server)
		{
			return new WrOracleServer(providerFactory, server);
		}

		/// <summary>
		/// Launch the database admin tool.
		/// </summary>
		/// <returns>True if launched successfully.</returns>
		public override bool RunAdministrator()
		{
			Process proc = Process.Start(GetAdminFilename());
			return (proc != null) && (!proc.HasExited);
		}

		private static readonly string[] keys = { "SOFTWARE\\ORACLE\\KEY_OraClient10g_home1", 
						"SOFTWARE\\ORACLE\\KEY_OraClient10g_home2",
						"SOFTWARE\\ORACLE\\KEY_OraClient10g_home3",
						"SOFTWARE\\ORACLE\\KEY_OraClient10g_home4",
						"SOFTWARE\\ORACLE\\KEY_OraClient10g_home5",
						"SOFTWARE\\ORACLE\\KEY_OraClient10g_home6",
						"SOFTWARE\\ORACLE\\KEY_OraClient10g_home7",
						"SOFTWARE\\ORACLE\\KEY_XE"};
		private static string GetAdminFilename()
		{
			foreach (string key in keys)
			{
				string exe = GetExe(key);
				if (exe != null)
				{
					string file = Path.Combine(exe, "BIN\\oemapp.bat");
					if (File.Exists(file))
						return file;
					file = Path.Combine(exe, "Database_homepage.url");
					if (File.Exists(file))
						return file;
				}
			}
			return null;
		}

		private static string GetExe(string path)
		{

			RegistryKey key = Registry.LocalMachine.OpenSubKey(path);
			if (key == null)
				return null;
			string exe = (string)key.GetValue("ORACLE_HOME");
			if (exe == null)
				return null;
			key.Close();
			return exe;
		}

		#endregion
	}
}
